/*
*  Copyright 2019-2020 Zheng Jie
*
*  Licensed under the Apache License, Version 2.0 (the "License");
*  you may not use this file except in compliance with the License.
*  You may obtain a copy of the License at
*
*  http://www.apache.org/licenses/LICENSE-2.0
*
*  Unless required by applicable law or agreed to in writing, software
*  distributed under the License is distributed on an "AS IS" BASIS,
*  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
*  See the License for the specific language governing permissions and
*  limitations under the License.
*/
package com.fzhucc.modules.expert.repository;

import com.fzhucc.modules.appletShow.domain.CmsService;
import com.fzhucc.modules.expert.domain.Expert;
import com.fzhucc.modules.pms.domain.Project;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import javax.validation.constraints.NotNull;
import java.util.List;

/**
* @website https://eladmin.vip
* @author yyg
* @date 2022-11-15
**/
public interface ExpertRepository extends JpaRepository<Expert, Integer>, JpaSpecificationExecutor<Expert> {


    @Query(nativeQuery = true, value = "select count(*) from tt_expert as e where e.type=:type and is_delete=0 ORDER BY sort desc")
    int findByType(String type);

    @Query(nativeQuery = true, value = "select count(*) from tt_expert as e where e.type=:type and is_delete=0 and is_show=1 ORDER BY sort desc")
    int findByXcxType(String type);

    @Query(nativeQuery = true, value = "select * from tt_expert where user_id=:userId and is_delete=0")
    Expert findByUserId(Long userId);

    boolean existsByPhone(String phone);

    @Query(nativeQuery = true, value = "select * from tt_expert where phone=:phone and org_area=:orgArea")
    List<Expert> existsAllByPhoneWithOrgArea(String phone,String orgArea);

    @Query(nativeQuery = true, value = "SELECT * from tt_expert as e where e.id NOT in(SELECT expect_id from pms_project_score where project_id =:projectId ) and e.type=:type and e.is_delete=0 ")
    List<Expert> findExpertByTypeWithprojectId(String type,String projectId);

    @Query(nativeQuery = true, value = "SELECT * from tt_expert where id NOT in(SELECT expect_id from pms_project_score where project_id =:projectId ) and is_delete=0 ")
    List<Expert> findExpertWithprojectId(String projectId);


    @Query(nativeQuery = true, value = "select * from tt_expert WHERE id!=:id1 and id!=:id2 and id!=:id3 and is_push =1")
    List<Expert> findWithOutIds(int id1, int id2, int id3);


    @Query(nativeQuery = true, value = "select * from tt_expert WHERE is_push =1 order by rand() limit 3")
    List<Expert> findRandom();

    @Query(nativeQuery = true, value = "select * from tt_expert WHERE is_push =1 and id!=:id1 and id!=:id2 and id!=:id3 order by rand() limit 3")
    List<Expert> findRandomWithOutIds(int id1,int id2,int id3);

    @Query(nativeQuery = true, value = "select * from tt_expert WHERE is_delete=0")
    List<Expert> findAlls();

    @Query(nativeQuery = true, value = "select * from tt_expert WHERE is_delete=0 and is_pull=1")
    List<Expert> findAllsPull();

    @Query(nativeQuery = true, value = "select * from tt_expert where rexpertid=:rexpertid")
    Expert findByRSciId(String rexpertid);

    @Query(nativeQuery = true, value = "select * from tt_expert WHERE is_delete=0 and xf is not null ")
    Page<Expert> findAllsWithPage(Pageable pageable);
}